using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using OfficeOpenXml;
using System.Linq;
using AvaAI批量点评系统.Models;

namespace AvaAI批量点评系统.Services;

public class ExcelService
{
    public ExcelService()
    {
        // 许可证在App.Initialize()中设置
    }

    public async Task<string> CreateStudentCommentsExcelAsync(List<StudentComment> comments, string fileName)
    {
        try
        {
            var desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            var filePath = Path.Combine(desktopPath, fileName);

            using var package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("学生评语");

            // 设置标题行
            worksheet.Cells[1, 1].Value = "学生姓名";
            worksheet.Cells[1, 2].Value = "课程名称";
            worksheet.Cells[1, 3].Value = "开始年级";
            worksheet.Cells[1, 4].Value = "结束年级";
            worksheet.Cells[1, 5].Value = "难度等级";
            worksheet.Cells[1, 6].Value = "AI生成评语";

            // 设置标题行样式
            using var range = worksheet.Cells[1, 1, 1, 6];
            range.Style.Font.Bold = true;
            range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);

            // 批量填充数据 - 性能优化
            if (comments.Count > 0)
            {
                var dataRange = worksheet.Cells[2, 1, comments.Count + 1, 6];
                var dataArray = new object[comments.Count, 6];

                for (int i = 0; i < comments.Count; i++)
                {
                    var comment = comments[i];
                    dataArray[i, 0] = comment.StudentName;
                    dataArray[i, 1] = comment.Subject;
                    dataArray[i, 2] = comment.StartGrade;
                    dataArray[i, 3] = comment.EndGrade;
                    dataArray[i, 4] = comment.Difficulty;
                    dataArray[i, 5] = comment.Comment;
                }

                dataRange.Value = dataArray;
            }

            // 自动调整列宽
            worksheet.Cells.AutoFitColumns();

            // 设置评语列宽度限制
            worksheet.Column(6).Width = 50;
            worksheet.Column(6).Style.WrapText = true;

            // 保存文件
            await package.SaveAsAsync(new FileInfo(filePath));
            return filePath;
        }
        catch (Exception ex)
        {
            throw new Exception($"创建Excel文件失败: {ex.Message}");
        }
    }

    public async Task<List<string>> ReadStudentNamesFromExcelAsync(string filePath)
    {
        return await Task.Run(() =>
        {
            try
            {
                var studentNames = new List<string>();
                
                using var package = new ExcelPackage(new FileInfo(filePath));
                var worksheet = package.Workbook.Worksheets[0];
                
                var rowCount = worksheet.Dimension?.Rows ?? 0;
                
                // 假设第一列是学生姓名，第一行是标题
                for (int row = 2; row <= rowCount; row++)
                {
                    var name = worksheet.Cells[row, 1].Value?.ToString();
                    if (!string.IsNullOrWhiteSpace(name))
                    {
                        studentNames.Add(name);
                    }
                }
                
                return studentNames;
            }
            catch (Exception ex)
            {
                throw new Exception($"读取Excel文件失败: {ex.Message}");
            }
        });
    }

    public async Task<string> CreateTemplateExcelAsync(string fileName)
    {
        try
        {
            var desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            var filePath = Path.Combine(desktopPath, fileName);

            using var package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("学生信息模板");

            // 设置模板标题行
            worksheet.Cells[1, 1].Value = "学生姓名";
            worksheet.Cells[1, 2].Value = "课程名称";
            worksheet.Cells[1, 3].Value = "年级";
            worksheet.Cells[1, 4].Value = "难度等级";
            worksheet.Cells[1, 5].Value = "原始评语";

            // 设置标题行样式
            using var range = worksheet.Cells[1, 1, 1, 5];
            range.Style.Font.Bold = true;
            range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);

            // 添加示例数据
            worksheet.Cells[2, 1].Value = "张三";
            worksheet.Cells[2, 2].Value = "乐高机器人搭建";
            worksheet.Cells[2, 3].Value = "三年级";
            worksheet.Cells[2, 4].Value = "入门";
            worksheet.Cells[2, 5].Value = "学习认真，动手能力强，需要加强创新思维";

            // 自动调整列宽
            worksheet.Cells.AutoFitColumns();

            // 保存文件
            await package.SaveAsAsync(new FileInfo(filePath));
            return filePath;
        }
        catch (Exception ex)
        {
            throw new Exception($"创建模板文件失败: {ex.Message}");
        }
    }

    // 新增：快速生成简化版Excel（只包含学生姓名，不生成AI评语）
    public async Task<string> CreateQuickStudentListExcelAsync(List<string> studentNames, string courseName, string grade, string difficulty, string fileName)
    {
        try
        {
            var desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            var filePath = Path.Combine(desktopPath, fileName);

            using var package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("学生名单");

            // 设置标题行
            worksheet.Cells[1, 1].Value = "序号";
            worksheet.Cells[1, 2].Value = "学生姓名";
            worksheet.Cells[1, 3].Value = "课程名称";
            worksheet.Cells[1, 4].Value = "年级";
            worksheet.Cells[1, 5].Value = "难度等级";
            worksheet.Cells[1, 6].Value = "备注";

            // 设置标题行样式
            using var headerRange = worksheet.Cells[1, 1, 1, 6];
            headerRange.Style.Font.Bold = true;
            headerRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            headerRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);

            // 批量填充数据
            if (studentNames.Count > 0)
            {
                var dataRange = worksheet.Cells[2, 1, studentNames.Count + 1, 6];
                var dataArray = new object[studentNames.Count, 6];

                for (int i = 0; i < studentNames.Count; i++)
                {
                    dataArray[i, 0] = i + 1; // 序号
                    dataArray[i, 1] = studentNames[i];
                    dataArray[i, 2] = courseName;
                    dataArray[i, 3] = grade;
                    dataArray[i, 4] = difficulty;
                    dataArray[i, 5] = "待填写"; // 备注
                }

                dataRange.Value = dataArray;
            }

            // 设置表格边框
            var tableRange = worksheet.Cells[1, 1, studentNames.Count + 1, 6];
            tableRange.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
            tableRange.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
            tableRange.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
            tableRange.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

            // 自动调整列宽
            worksheet.Cells.AutoFitColumns();

            // 添加汇总信息
            var summaryRow = studentNames.Count + 3;
            worksheet.Cells[summaryRow, 1].Value = "汇总信息:";
            worksheet.Cells[summaryRow, 1].Style.Font.Bold = true;
            worksheet.Cells[summaryRow + 1, 1].Value = $"总学生数: {studentNames.Count}人";
            worksheet.Cells[summaryRow + 2, 1].Value = $"课程名称: {courseName}";
            worksheet.Cells[summaryRow + 3, 1].Value = $"年级: {grade}";
            worksheet.Cells[summaryRow + 4, 1].Value = $"难度等级: {difficulty}";
            worksheet.Cells[summaryRow + 5, 1].Value = $"生成时间: {DateTime.Now:yyyy-MM-dd HH:mm:ss}";

            // 保存文件
            await package.SaveAsAsync(new FileInfo(filePath));
            return filePath;
        }
        catch (Exception ex)
        {
            throw new Exception($"创建快速Excel文件失败: {ex.Message}");
        }
    }

    /// <summary>
    /// 基于模板创建包含能力评语的学生表格
    /// </summary>
    public async Task<List<string>> CreateStudentCommentsWithAbilityFromTemplateAsync(List<StudentComment> comments, string courseName, string grade)
    {
        var templateService = new TemplateService();
        var generatedFiles = new List<string>();

        if (!templateService.IsTemplateExists())
        {
            throw new FileNotFoundException("模板资源不存在，无法生成表格");
        }

        // 创建统一的输出文件夹
        var desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
        var timestamp = DateTime.Now.ToString("yyyyMMdd_HHmmss");
        var folderName = $"{courseName}_修改后评价表_{timestamp}";
        var outputFolderPath = Path.Combine(desktopPath, folderName);

        if (!Directory.Exists(outputFolderPath))
        {
            Directory.CreateDirectory(outputFolderPath);
        }

        // 为每个学生生成基于模板的表格
        foreach (var comment in comments)
        {
            try
            {
                var filePath = await CreateSingleStudentTemplateFileAsync(comment, outputFolderPath, templateService);
                generatedFiles.Add(filePath);
            }
            catch (Exception ex)
            {
                throw new Exception($"为学生 {comment.StudentName} 生成表格失败: {ex.Message}");
            }
        }

        return generatedFiles;
    }

    /// <summary>
    /// 为单个学生创建基于模板的表格文件
    /// </summary>
    private async Task<string> CreateSingleStudentTemplateFileAsync(StudentComment comment, string outputFolderPath, TemplateService templateService)
    {
        var gradeRange = ConvertToGradeRange(comment.StartGrade);
        var outputFileName = $"{comment.Subject}_{comment.StudentName}_{gradeRange}_评价表.xlsx";
        var outputPath = Path.Combine(outputFolderPath, outputFileName);

        // 从模板创建临时文件
        var tempTemplatePath = await templateService.CreateTempTemplateFileAsync();
        try
        {
            File.Copy(tempTemplatePath, outputPath, true);
        }
        finally
        {
            // 清理临时文件
            if (File.Exists(tempTemplatePath))
            {
                File.Delete(tempTemplatePath);
            }
        }

        // 打开文件并填入数据
        using var package = new ExcelPackage(new FileInfo(outputPath));
        var worksheet = package.Workbook.Worksheets[0];

        // 填写基础信息
        await FillBasicInformationFromComment(worksheet, comment);

        // 填写AI评语
        FillAIComment(worksheet, comment.Comment);

        // 填写能力评语（如果有的话）
        if (comment.AbilityEvaluation?.EvaluationItems?.Count > 0)
        {
            FillAbilityEvaluationToTemplate(worksheet, comment.AbilityEvaluation);
        }

        await package.SaveAsync();
        return outputPath;
    }

    /// <summary>
    /// 填写基础信息到模板
    /// </summary>
    private async Task FillBasicInformationFromComment(ExcelWorksheet worksheet, StudentComment comment)
    {
        // 填写课程名称
        worksheet.Cells["A1"].Value = comment.Subject;

        // 填写学生姓名
        worksheet.Cells["B3"].Value = comment.StudentName;

        // 填写年级范围
        var gradeRange = ConvertToGradeRange(comment.StartGrade);
        worksheet.Cells["D3"].Value = gradeRange;

        // 填写评级时间
        var currentDate = DateTime.Now.ToString("yyyy年MM月dd日");
        worksheet.Cells["F3"].Value = currentDate;
    }

    /// <summary>
    /// 填写AI评语到模板
    /// </summary>
    private void FillAIComment(ExcelWorksheet worksheet, string comment)
    {
        // 根据模板结构，AI评语通常在A22位置
        worksheet.Cells["A22"].Value = comment;
    }

    /// <summary>
    /// 将能力评语填入模板的相应位置
    /// </summary>
    private void FillAbilityEvaluationToTemplate(ExcelWorksheet worksheet, StudentAbilityEvaluation evaluation)
    {
        // 根据模板结构，能力评语表格从第5行开始
        int startRow = 5;

        foreach (var item in evaluation.EvaluationItems)
        {
            // 查找对应的能力项目行
            var targetRow = FindAbilityRowInTemplate(worksheet, item.Category, item.SubCategory, startRow);
            if (targetRow > 0)
            {
                // 填入修改后的评语内容（使用用户修改过的内容）
                FillModifiedAbilityItemToRow(worksheet, targetRow, item);
            }
        }
    }

    /// <summary>
    /// 在模板中查找对应的能力项目行
    /// </summary>
    private int FindAbilityRowInTemplate(ExcelWorksheet worksheet, string category, string subCategory, int startRow)
    {
        // 在模板中查找匹配的行
        for (int row = startRow; row <= 50; row++) // 假设能力评语在前50行内
        {
            var categoryCell = worksheet.Cells[row, 1].Value?.ToString();
            var subCategoryCell = worksheet.Cells[row, 2].Value?.ToString();

            if (!string.IsNullOrEmpty(categoryCell) && !string.IsNullOrEmpty(subCategoryCell))
            {
                // 模糊匹配类别和子类别
                if (categoryCell.Contains(category.Replace(".", "")) || category.Contains(categoryCell))
                {
                    if (subCategoryCell.Contains(subCategory) || subCategory.Contains(subCategoryCell))
                    {
                        return row;
                    }
                }
            }
        }
        return -1; // 未找到
    }

    /// <summary>
    /// 将能力评价项目填入指定行
    /// </summary>
    private void FillAbilityItemToRow(ExcelWorksheet worksheet, int row, AbilityEvaluationItem item)
    {
        // 填入各等级的评语（假设A-在第3列，A在第4列，A+在第5列）
        worksheet.Cells[row, 3].Value = item.LevelAMinus;
        worksheet.Cells[row, 4].Value = item.LevelA;
        worksheet.Cells[row, 5].Value = item.LevelAPlus;

        // 填入选择的等级（假设在第6列）
        worksheet.Cells[row, 6].Value = item.SelectedLevel;

        // 高亮选中的等级
        HighlightSelectedLevelInTemplate(worksheet, row, item.SelectedLevel);
    }

    /// <summary>
    /// 将修改过的能力评价项目填入指定行（使用用户修改的内容）
    /// </summary>
    private void FillModifiedAbilityItemToRow(ExcelWorksheet worksheet, int row, AbilityEvaluationItem item)
    {
        // 填入修改后的各等级评语（使用用户修改过的内容）
        worksheet.Cells[row, 3].Value = item.LevelAMinus;
        worksheet.Cells[row, 4].Value = item.LevelA;
        worksheet.Cells[row, 5].Value = item.LevelAPlus;

        // 填入选择的等级
        worksheet.Cells[row, 6].Value = item.SelectedLevel;

        // 高亮选中的等级
        HighlightSelectedLevelInTemplate(worksheet, row, item.SelectedLevel);
    }

    /// <summary>
    /// 在模板中高亮选中的等级
    /// </summary>
    private void HighlightSelectedLevelInTemplate(ExcelWorksheet worksheet, int row, string selectedLevel)
    {
        int columnIndex = selectedLevel switch
        {
            "A-" => 3,
            "A" => 4,
            "A+" => 5,
            _ => 4
        };

        var cell = worksheet.Cells[row, columnIndex];
        cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        cell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);
        cell.Style.Font.Bold = true;

        // 评分列也高亮
        var gradeCell = worksheet.Cells[row, 6];
        gradeCell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        gradeCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGreen);
        gradeCell.Style.Font.Bold = true;
    }

    /// <summary>
    /// 转换年级格式
    /// </summary>
    private string ConvertToGradeRange(string grade)
    {
        var gradeNumber = grade switch
        {
            "一年级" => "1",
            "二年级" => "2",
            "三年级" => "3",
            "四年级" => "4",
            "五年级" => "5",
            "六年级" => "6",
            _ => "3"
        };
        return $"{gradeNumber}-{gradeNumber}年级";
    }

    /// <summary>
    /// 为单个学生创建工作表
    /// </summary>
    private async Task CreateStudentWorksheet(ExcelWorksheet worksheet, StudentComment comment)
    {
        // 设置学生基本信息
        worksheet.Cells["A1"].Value = "学生姓名:";
        worksheet.Cells["B1"].Value = comment.StudentName;
        worksheet.Cells["A2"].Value = "课程名称:";
        worksheet.Cells["B2"].Value = comment.Subject;
        worksheet.Cells["A3"].Value = "年级:";
        worksheet.Cells["B3"].Value = comment.StartGrade;
        worksheet.Cells["A4"].Value = "难度等级:";
        worksheet.Cells["B4"].Value = comment.Difficulty;

        // 设置基本信息样式
        var infoRange = worksheet.Cells["A1:B4"];
        infoRange.Style.Font.Bold = true;
        infoRange.Style.Font.Size = 12;

        // AI生成评语部分
        worksheet.Cells["A6"].Value = "AI生成评语:";
        worksheet.Cells["A6"].Style.Font.Bold = true;
        worksheet.Cells["A6"].Style.Font.Size = 14;

        worksheet.Cells["A7"].Value = comment.Comment;
        worksheet.Cells["A7:F10"].Merge = true;
        worksheet.Cells["A7"].Style.WrapText = true;
        worksheet.Cells["A7"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
        worksheet.Cells["A7"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);

        // 能力评语表格
        if (comment.AbilityEvaluation?.EvaluationItems?.Count > 0)
        {
            await CreateAbilityEvaluationTable(worksheet, comment.AbilityEvaluation, 12);
        }

        // 设置列宽
        worksheet.Column(1).Width = 15;
        worksheet.Column(2).Width = 15;
        worksheet.Column(3).Width = 30;
        worksheet.Column(4).Width = 30;
        worksheet.Column(5).Width = 30;
        worksheet.Column(6).Width = 10;
    }

    /// <summary>
    /// 创建能力评语表格
    /// </summary>
    private async Task CreateAbilityEvaluationTable(ExcelWorksheet worksheet, StudentAbilityEvaluation evaluation, int startRow)
    {
        // 标题
        worksheet.Cells[startRow, 1].Value = "能力评语表";
        worksheet.Cells[startRow, 1, startRow, 6].Merge = true;
        worksheet.Cells[startRow, 1].Style.Font.Bold = true;
        worksheet.Cells[startRow, 1].Style.Font.Size = 14;
        worksheet.Cells[startRow, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

        // 表头
        var headerRow = startRow + 2;
        worksheet.Cells[headerRow, 1].Value = "评价项目";
        worksheet.Cells[headerRow, 2].Value = "评价内容";
        worksheet.Cells[headerRow, 3].Value = "A-";
        worksheet.Cells[headerRow, 4].Value = "A";
        worksheet.Cells[headerRow, 5].Value = "A+";
        worksheet.Cells[headerRow, 6].Value = "评分";

        // 设置表头样式
        var headerRange = worksheet.Cells[headerRow, 1, headerRow, 6];
        headerRange.Style.Font.Bold = true;
        headerRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        headerRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
        headerRange.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);

        // 填充数据
        int currentRow = headerRow + 1;
        string currentCategory = "";
        int categoryStartRow = currentRow;

        foreach (var item in evaluation.EvaluationItems)
        {
            // 处理大类别合并
            if (item.Category != currentCategory)
            {
                if (!string.IsNullOrEmpty(currentCategory) && currentRow > categoryStartRow)
                {
                    var categoryCell = worksheet.Cells[categoryStartRow, 1, currentRow - 1, 1];
                    categoryCell.Merge = true;
                    categoryCell.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                }
                currentCategory = item.Category;
                categoryStartRow = currentRow;
            }

            // 填充数据
            worksheet.Cells[currentRow, 1].Value = item.Category;
            worksheet.Cells[currentRow, 2].Value = item.SubCategory;
            worksheet.Cells[currentRow, 3].Value = item.LevelAMinus;
            worksheet.Cells[currentRow, 4].Value = item.LevelA;
            worksheet.Cells[currentRow, 5].Value = item.LevelAPlus;
            worksheet.Cells[currentRow, 6].Value = item.SelectedLevel;

            // 高亮选中的等级
            HighlightSelectedLevel(worksheet, currentRow, item.SelectedLevel);

            // 设置行样式
            var rowRange = worksheet.Cells[currentRow, 1, currentRow, 6];
            rowRange.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            rowRange.Style.WrapText = true;
            worksheet.Row(currentRow).Height = 40;

            currentRow++;
        }

        // 处理最后一个类别的合并
        if (!string.IsNullOrEmpty(currentCategory) && currentRow > categoryStartRow)
        {
            var categoryCell = worksheet.Cells[categoryStartRow, 1, currentRow - 1, 1];
            categoryCell.Merge = true;
            categoryCell.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
        }
    }

    /// <summary>
    /// 高亮选中的等级
    /// </summary>
    private void HighlightSelectedLevel(ExcelWorksheet worksheet, int row, string selectedLevel)
    {
        int columnIndex = selectedLevel switch
        {
            "A-" => 3,
            "A" => 4,
            "A+" => 5,
            _ => 4
        };

        var cell = worksheet.Cells[row, columnIndex];
        cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        cell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);
        cell.Style.Font.Bold = true;

        // 评分列也高亮
        var gradeCell = worksheet.Cells[row, 6];
        gradeCell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        gradeCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGreen);
        gradeCell.Style.Font.Bold = true;
        gradeCell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
    }

    /// <summary>
    /// 创建汇总工作表
    /// </summary>
    private void CreateSummaryWorksheet(ExcelWorksheet worksheet, List<StudentComment> comments)
    {
        // 标题
        worksheet.Cells["A1"].Value = "学生评语汇总表";
        worksheet.Cells["A1:F1"].Merge = true;
        worksheet.Cells["A1"].Style.Font.Bold = true;
        worksheet.Cells["A1"].Style.Font.Size = 16;
        worksheet.Cells["A1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

        // 表头
        worksheet.Cells["A3"].Value = "学生姓名";
        worksheet.Cells["B3"].Value = "课程名称";
        worksheet.Cells["C3"].Value = "年级";
        worksheet.Cells["D3"].Value = "难度等级";
        worksheet.Cells["E3"].Value = "AI生成评语";
        worksheet.Cells["F3"].Value = "能力评语状态";

        // 设置表头样式
        var headerRange = worksheet.Cells["A3:F3"];
        headerRange.Style.Font.Bold = true;
        headerRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        headerRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);

        // 填充数据
        for (int i = 0; i < comments.Count; i++)
        {
            var comment = comments[i];
            var row = i + 4;

            worksheet.Cells[row, 1].Value = comment.StudentName;
            worksheet.Cells[row, 2].Value = comment.Subject;
            worksheet.Cells[row, 3].Value = comment.StartGrade;
            worksheet.Cells[row, 4].Value = comment.Difficulty;
            worksheet.Cells[row, 5].Value = comment.Comment;
            worksheet.Cells[row, 6].Value = comment.AbilityEvaluation?.EvaluationItems?.Count > 0 ? "已配置" : "未配置";
        }

        // 设置列宽
        worksheet.Column(1).Width = 15;
        worksheet.Column(2).Width = 20;
        worksheet.Column(3).Width = 10;
        worksheet.Column(4).Width = 15;
        worksheet.Column(5).Width = 50;
        worksheet.Column(6).Width = 15;
    }
}

public class StudentComment
{
    public string StudentName { get; set; } = string.Empty;
    public string Subject { get; set; } = string.Empty;
    public string StartGrade { get; set; } = string.Empty;
    public string EndGrade { get; set; } = string.Empty;
    public string Difficulty { get; set; } = string.Empty;
    public string Comment { get; set; } = string.Empty;
    public StudentAbilityEvaluation? AbilityEvaluation { get; set; }
}